const express = require('express');
const mysql = require('mysql2/promise');
const cors = require('cors');
const app = express();

// Enable CORS for all routes
app.use(cors());
app.use(express.json());

// MySQL database configuration
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  // password: '123456',
  password: 'EiEE2YNLi6m6kzxC',
  database: 'vocabularyapp',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// Get all words not yet learned with pagination
app.get('/api/words/learnedList', async (req, res) => {
  try {
    const page = parseInt(req.query.page) || 1; // Default to 1 if not provided
    const size = parseInt(req.query.size) || 10; // Default to 10 if not provided
    const offset = (page - 1) * size;

    // 查询未学习的单词
    const [unlearnedWords] = await pool.query('SELECT * FROM Words WHERE learned = 0 ORDER BY id LIMIT ? OFFSET ?', [size, offset]);

    // 查询所有单词的数量
    const [totalCountRows] = await pool.query('SELECT COUNT(*) AS total FROM Words');
    const totalCount = totalCountRows[0].total;

    // 查询未学习单词的数量
    const [unlearnedCountRows] = await pool.query('SELECT COUNT(*) AS unlearned_count FROM Words WHERE learned = 0');
    const unlearnedCount = unlearnedCountRows[0].unlearned_count;

    // 查询已学习单词的数量
    const [learnedCountRows] = await pool.query('SELECT COUNT(*) AS learned_count FROM Words WHERE learned = 1');
    const learnedCount = learnedCountRows[0].learned_count;

    // 构建响应对象
    const response = {
      words: unlearnedWords,
      totalCount: totalCount,
      unlearnedCount: unlearnedCount,
      learnedCount: learnedCount
    };

    res.json(response);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'An error occurred while fetching data.' });
  }
});
app.get('/api/words/unlearnedList', async (req, res) => {
  try {
    const page = parseInt(req.query.page) || 1; // Default to 1 if not provided
    const size = parseInt(req.query.size) || 10; // Default to 10 if not provided
    const offset = (page - 1) * size;

    // 查询已学习的单词
    const [unlearnedWords] = await pool.query('SELECT * FROM Words WHERE learned = 1 ORDER BY updatedAt LIMIT ? OFFSET ?', [size, offset]);

    // 查询所有单词的数量
    const [totalCountRows] = await pool.query('SELECT COUNT(*) AS total FROM Words');
    const totalCount = totalCountRows[0].total;

    // 查询未学习单词的数量
    const [unlearnedCountRows] = await pool.query('SELECT COUNT(*) AS unlearned_count FROM Words WHERE learned = 0');
    const unlearnedCount = unlearnedCountRows[0].unlearned_count;

    // 查询已学习单词的数量
    const [learnedCountRows] = await pool.query('SELECT COUNT(*) AS learned_count FROM Words WHERE learned = 1');
    const learnedCount = learnedCountRows[0].learned_count;

    // 构建响应对象
    const response = {
      words: unlearnedWords,
      totalCount: totalCount,
      unlearnedCount: unlearnedCount,
      learnedCount: learnedCount
    };

    res.json(response);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'An error occurred while fetching data.' });
  }
});

// Search words with pagination
app.get('/api/words/search', async (req, res) => {
  const search = req.query.q;
  const page = parseInt(req.query.page) || 1; // Default to 1 if not provided
  const size = parseInt(req.query.size) || 10; // Default to 10 if not provided
  const offset = (page - 1) * size;

  try {
    const [rows] = await pool.query(
      `SELECT * FROM Words WHERE (word LIKE ? OR meaning LIKE ?) ORDER BY id LIMIT ? OFFSET ?`,
      [`%${search}%`, `%${search}%`, size, offset]
    );

    // 查询符合条件的单词总数
    const [totalCountRows] = await pool.query(
      `SELECT COUNT(*) AS total FROM Words WHERE (word LIKE ? OR meaning LIKE ?)`,
      [`%${search}%`, `%${search}%`]
    );
    const totalCount = totalCountRows[0].total;

    res.json({ words: rows, totalCount });
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'An error occurred while searching data.' });
  }
});

// Mark a word as learned
app.patch('/api/words/learned/:id', async (req, res) => {
  const { id } = req.params;
  await pool.query('UPDATE Words SET learned = 1 WHERE id = ?', [id]);
  res.status(200).json({ message: 'Word marked as learned' });
});
app.patch('/api/words/unlearned/:id', async (req, res) => {
  const { id } = req.params;
  await pool.query('UPDATE Words SET learned = 0 WHERE id = ?', [id]);
  res.status(200).json({ message: 'Word marked as learned' });
});
//设置颜色
app.patch('/api/words/color/:id', async (req, res) => {
  const { id } = req.params;
  const { color } = req.body; // 获取从请求体中传来的 color 值
  await pool.query('UPDATE Words SET color = ? WHERE id = ?', [color, id]);
  res.status(200).json({ message: 'Word updated successfully' });
});
//修改中文备注
app.patch('/api/words/meaning/:id', async (req, res) => {
  const { id } = req.params;
  const { meaning } = req.body; // 获取从请求体中传来的 color 值
  await pool.query('UPDATE Words SET meaning = ? WHERE id = ?', [meaning, id]);
  res.status(200).json({ message: 'Word updated successfully' });
});
//设置显示中文
app.patch('/api/words/show_chinese/:id', async (req, res) => {
  const { id } = req.params;
  const { show_chinese } = req.body; // 获取从请求体中传来的 show_chinese 值
  await pool.query('UPDATE Words SET show_chinese = ? WHERE id = ?', [show_chinese, id]);
  res.status(200).json({ message: 'Word updated successfully' });
});


// Batch add words to the database
app.post('/api/words/batch', async (req, res) => {
  const words = req.body;
  try {
    // Validate the words before insertion
    for (const word of words) {
      if (!word.word || typeof word.word !== 'string') {
        throw new Error('Invalid word format');
      }
    }

    // Prepare the insert query
    const placeholders = words.map(() => '(?, ?, ?, ?)').join(',');
    const query = `INSERT INTO Words (word, pronunciation, meaning, learned) VALUES ${placeholders}`;

    // Execute the insert query
    const values = words.flatMap(word => [word.word, word.pronunciation, word.meaning, 0]);
    const [result] = await pool.query(query, values);

    res.status(201).json({ inserted: result.affectedRows });
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

app.listen(3000, () => console.log('Server running on port 3000'));